Comparing syntax

https://gyazo.com/ed6df50c3b2e854f351aa2a1327e18ad

Comparing syntax (Sql2o and JDBI)

We comparing syntax of Sorm4j with those of Sql2o and JDBI. Both products are widly used and have similar scope to Sorm4j.

Situation

code:schema.sql

CREATE TABLE post (id INT PRIMARY KEY, text VARCHAR(255), creation_date DATETIME, last_change_date DATETIME, counter1 INT, counter2 DOUBLE);

code:post.java

public static class Post {

private int id;

private String text;

private Integer counter1;

private Double counter2;

private Timestamp creationDate;

private Timestamp lastChangeDate;

// getters and setters here

}

/yuunkjm/--.icon

Create entry point

code:precond.java

private static final javax.sql.DataSource dataSource;

code:sorm4j.java

private static final Sorm sorm = Sorm.create(dataSource);

code:sql2o.java

private static Sql2o sql2o = new Sql2o(dataSource);

// or use Query#setAutoDeriveColumnNames(true)

static {

sql2o.setDefaultColumnMappings(

Map.of("creation_date", "creationDate", "last_change_date", "lastChangeDate"));

}

code:jdbi.java

private static final Jdbi jdbi = Jdbi.create(dataSource);

Sorm4j and JDBI supports snake case to camel case conversion by default.

/yuunkjm/--.icon

Select one row

code:sorm4j.java

public Post selectOneRow(int input) {

return sorm.readFirst(Post.class, SELECT_TYPICAL_SQL + " WHERE id=?", input);

}

code:sql2o.java

public Post selectOneRow(int input) {

try (org.sql2o.Connection conn = sql2o.open()) {

Query query =

conn.createQuery(SELECT_TYPICAL_SQL + " WHERE id = :id").setAutoDeriveColumnNames(true);

return query.addParameter("id", input).executeAndFetchFirst(Post.class);

}

}

code:jdbi.java

public Post selectOneRow(int input) {

return jdbi.withHandle(handler -> handler.createQuery(SELECT_TYPICAL_SQL + " WHERE id=?")

.bind(0, input).mapToBean(Post.class).findFirst()).get();

}

Sorm4j and JDBI support named parameters and ordered parameters.

Sql2o supports named parameters.

The three library could the result of query to a POJO.

/yuunkjm/--.icon

Select all

code:sorm4j.java

public List<Post> selectAll(int input) {

return sorm.readList(Post.class, SELECT_TYPICAL_SQL);

}

code:sql2o.java

public List<Post> selectAll(int input) {

try (org.sql2o.Connection conn = sql2o.open()) {

Query query = conn.createQuery(SELECT_TYPICAL_SQL).setAutoDeriveColumnNames(true);

return query.executeAndFetch(Post.class);

}

}

code:jdbi.java

public List<Post> selectAll(int input) {

return jdbi.withHandle(

handler -> handler.createQuery(SELECT_TYPICAL_SQL).mapToBean(Post.class).list());

}

The three library could the result of query to a list of POJO.

/yuunkjm/--.icon

Insert

code:precond.sql

insert into post(?,?,?,?,?) values (:text,:creationDate,:lastChangeDate,:counter1,:counter2)

code:sorm4j.java

public int insert(Post input) {

return sorm.insert(input);

}

code:sql2o.java

public int insert(Post input) {

try (org.sql2o.Connection conn = sql2o.open()) {

return conn.createQuery(insertSqlWithNamedParameter).bind(input).executeUpdate()

.getResult();

}

}

code:jdbi.java

public int insert(Post input) {

return jdbi.withHandle(

handler -> handler.createUpdate(insertSqlWithNamedParameter).bindBean(input).execute());

}

Sorm4j inserts object POJO directly because it generates the insert statement internally.

Sql2o and JDBI use a insert statement and bind parameters by POJO (bean).

/yuunkjm/--.icon

multirow insert

code:precond.sql

insert into post(?,?,?,?,?) values (:text,:creationDate,:lastChangeDate,:counter1,:counter2)

code:sorm4.java

public void multiRowInsert(Post... inputs) {

sorm.insert(inputs);

}

code:sql2o.java

public void multiRowInsert(Post... inputs) {

try (org.sql2o.Connection conn = sql2o.open()) {

Query query = conn.createQuery(insertSqlWithNamedParameter);

for (Post a : inputs) {

query.bind(a).addToBatch();

}

query.executeBatch();

query.close();

}

}

code:jdbi.java

public void multiRowInsert(Post... inputs) {

jdbi.useHandle(handle -> {

PreparedBatch batch = handle.prepareBatch(insertSqlWithNamedParameter);

for (Post a : inputs) {

batch.bindBean(a).add();

}

batch.execute();

});

}

Sorm4j inserts array of POJO directly because it generates the multirow insert statement internally.

Sql2o and JDBI execute batch.

Comparing syntax (read)

We compare syntax for reading table. You need more information about the other functions

/yuunkjm/**.icon

Hand Coded

code:java

try (Connection conn = dataSource.getConnection();

PreparedStatement stmt = conn.prepareStatement(SELECT_TYPICAL_SQL + " WHERE id = ?")) {

stmt.setInt(1, input);

try (ResultSet rs = stmt.executeQuery()) {

while (rs.next()) {

Post p = new Post();

p.setId(rs.getInt("id"));

p.setText(rs.getString("text"));

(snip)

}

}

} catch (SQLException e) {

throw new RuntimeException("error when executing query", e);

}

}

Sorm4j

code:java

sorm.selectByPrimaryKey(Post.class, input);

sorm.readFirst(Post.class, Post.SELECT_TYPICAL_SQL + " WHERE id=?", input);

Sql2o

code:java

try (org.sql2o.Connection conn = sql2o.open()) {

Query query =

conn.createQuery(SELECT_TYPICAL_SQL + " WHERE id = :id").setAutoDeriveColumnNames(true);

query.addParameter("id", input).executeAndFetchFirst(Post.class);

}

Spring JDBC template

code:java

jdbcTemplate.queryForObject(SELECT_TYPICAL_SQL + " WHERE id = :id",

Collections.singletonMap("id", input), new BeanPropertyRowMapper<Post>(Post.class));

MyBatis

code:java

try (SqlSession session = sqlSessionFactory.openSession()) {

session.getMapper(MyBatisPostMapper.class).selectPost(input);

}

private interface MyBatisPostMapper {

@Results({@Result(property = "creationDate", column = "creation_date"),

@Result(property = "lastChangeDate", column = "last_change_date")})

Post selectPost(int id);

}

JDBI

code:java

jdbi.withHandle(handler -> handler.createQuery(SELECT_TYPICAL_SQL + " WHERE id=?")

.bind(0, input).mapToBean(Post.class).findFirst()).get();

JOOQ

code:java

try (Connection conn = dataSource.getConnection()) {

DSLContext context = DSL.using(conn, SQLDialect.H2);

context.select().from("post").where("id = ?", input).fetchOne().into(Post.class);

} catch (SQLException e) {

e.printStackTrace();

}